﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace DLL
{
    public class StuinfoDAL
    {
        //public List<stuinfo> select(string name = "")
        //{
        //    return Model.stuinfo.Where(m => m.Name.Contains(name)).ToList();

        //}
        //public stuinfo selectread(int id)
        //{
        //    return Model.stuinfo.Find(id);
        //}
        //public int add(stuinfo info)
        //{
        //    Model.stuinfo.Add(info);
        //    return Model.SaveChanges();
        //}
        //public int delete(stuinfo info)
        //{
        //    Model.stuinfo.Remove(info);
        //    return Model.SaveChanges();
        //}
        //public int update(stuinfo info)
        //{
        //    var cha = Model.stuinfo.FirstOrDefault(m => m.Id == info.Id);
        //    if (cha != null)
        //    {
        //        cha.Name = info.Name;
        //        cha.Comment = info.Comment;
        //    }
        //    return Model.SaveChanges();
        //}

        //三层调用DBhelper方法实现增删改查(静态static)
        public static List<Stuinfo2> select(string name = "")
        {
            //sql语句，参数化（防sql注入）
            var sql = "select*from Stuinfo where name like @name";
            SqlParameter[] sqlpara = new SqlParameter[]
            {
                new SqlParameter("@name",$"%{name}%")
            };
            //调用DBHelper类的query方法，并传入sqlpara中的参数执行SQL语句，再存入datatable中
            DataTable dt = DBHelper.query(sql, sqlpara);
            //创建stuinfo的泛型对象
            List<Stuinfo2> list = new List<Stuinfo2>();
            //foreach循环遍历datatable中的每行的数据
            foreach (DataRow row in dt.Rows)
            {
                //将stuinfo中的值赋值给cla
                var cla = new Stuinfo2()
                {
                    //获取datatable表中每行数据的特定值
                    Id = Convert.ToInt32(row["Id"]),
                    Name = Convert.ToString(row["Name"]),
                    Comment = Convert.ToString(row["Comment"]),
                };
                //将从Model类stuinfo中获取的值添加到list泛型
                list.Add(cla);
            }
            return list;
        }
        public static Stuinfo2 selectread(int id)
        {
            var sql = "select*from stuinfo where id=@Id";
            SqlParameter[] sqlpara = new SqlParameter[]
            {
                new SqlParameter("@Id",$"{id}")
            };
            SqlDataReader reader = DBHelper.read(sql, sqlpara);
            Stuinfo2 info = null;
            if (reader.Read())
            {
                info = new Stuinfo2()
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = Convert.ToString(reader["Name"]),
                    Comment = Convert.ToString(reader["Comment"])
                };
            }
            return info;
        }
        public static int add(Stuinfo2 info)
        {
            var sql = "insert into stuinfo values('@name','@comment')";
            SqlParameter[] sqlpara = new SqlParameter[]
            {
                new SqlParameter("@name",info.Name),
                new SqlParameter("@comment",info.Comment)
            };
            return DBHelper.NonQuery(sql, sqlparameters: sqlpara);
        }
        public static int delete(int id)
        {
            var sql = "del_class";
            SqlParameter[] sqlpara = new SqlParameter[]
            {
                new SqlParameter("@id",$"{id}")
            };
            return DBHelper.NonQuery(sql, CommandType.StoredProcedure, sqlpara);
        }
        public static int update(Stuinfo2 info)
        {
            var sql = "update stuinfo set name=@name comment=@comment where id=@id";
            SqlParameter[] sqlpara = new SqlParameter[]
            {
                new SqlParameter("@id",info.Id),
                new SqlParameter("@name",info.Name),
                new SqlParameter("@comment",info.Comment)
            };
            return DBHelper.NonQuery(sql, sqlparameters: sqlpara);
        }
    }
}
